DROP VIEW [dbo].[vi0214]
GO
CREATE VIEW [dbo].[vi0214] AS 
SELECT tb0071.GroupID,tb0191.CreateUserID,tb0191.PrintCount,tb0104.WareHouseID,tb0191.DivisionID, tb0191.TranMonth, tb0191.TranYear,
	tb0191.VoucherID, tb0191.BatchID,	  
	tb0191.VoucherDate,tb0191.VoucherNo, Serial, InvoiceNo,
	tb0191.VoucherTypeID,
	VATTypeID, InvoiceDate,
	VDescription,
	BDescription,
	tb0191.CurrencyID,
	ExchangeRate,
	--Sum(isnull(OriginalAmount,0)) as OriginalAmount,
	--Sum(Isnull(ConvertedAmount,0)) as ConvertedAmount,
	Sum ( Case when TransactionTypeID ='T04' then OriginalAmount else 0 end ) as OriginalAmount,
	Sum ( Case when TransactionTypeID ='T04' then ConvertedAmount else 0 end ) as ConvertedAmount,

	tb0191.ObjectID,
	tb0045.ObjectName,
	--(Case when tb0045.IsUpdateName = 0 then tb0045.ObjectName else VATObjectName End) as  ObjectName,
	tb0191.VATObjectID,
	(Case when A.IsUpdateName = 0 then A.ObjectName else tb0191.VATObjectName End) as  VATObjectName,
	DueDate,
	tb0191.OrderID,
	isnull(IsStock,0) as IsStock, 
	isnull((Select sum(ConvertedAmount)  From tb0191 C Where C.VoucherID = tb0191.VoucherID and TransactionTypeID ='T54'),0)  as CommissionAmount,
	isnull((Select sum(ConvertedAmount)  From tb0191 C Where C.VoucherID = tb0191.VoucherID and TransactionTypeID ='T14'),0)  as TaxAmount
	
From tb0191 
left join tb0104 on tb0191.voucherID=tb0104.voucherID
inner join tb0045 on tb0045.ObjectID = tb0191.ObjectID
Left join tb0045 A on A.ObjectID = tb0191.VATObjectID
LEFT JOIN tb0071 ON tb0191.CreateUserID=tb0071.UserID


Where TransactionTypeID = 'T04' and tb0191.TableID in ( 'tb0191')

Group by  tb0071.GroupID,tb0191.CreateUserID,tb0191.PrintCount,tb0104.WareHouseID,tb0191.DivisionID, tb0191.TranMonth, tb0191.TranYear,
	tb0191.VoucherID, tb0191.BatchID,	  
	tb0191.VoucherDate,tb0191.VoucherNo, Serial, InvoiceNo,
	tb0191.VoucherTypeID, VATTypeID, InvoiceDate,
	VDescription,
	BDescription,
	tb0191.CurrencyID,
	ExchangeRate,	
	tb0191.ObjectID,
	tb0045.ObjectName,
	DueDate,tb0191.OrderID,
	tb0045.IsUpdateName, tb0191.VATObjectName,
	isnull(IsStock,0), tb0191.VATObjectID,A.ObjectName,A.IsUpdateName

GO